package comparedatabase;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

public class DbUtil {

    /**
     * 获取当前链接的schema下的所有的表
     * @param conn
     * @return
     * @throws SQLException
     */
    public static Set<String> getTables(Connection conn) throws SQLException {
        Set<String> set = new HashSet<>();
        try(Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = DATABASE()")) {
            while (rs.next()) {
                set.add(rs.getString("TABLE_NAME"));
            }
        }
        return set;
    }

    /**
     * 获取当前链接的schema下的所有的视图
     * @param conn
     * @return
     * @throws SQLException
     */
    public static Set<String> getViews(Connection conn) throws SQLException {
        Set<String> set = new HashSet<>();
        try(Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_TYPE = 'VIEW' AND TABLE_SCHEMA = DATABASE()")) {
            while (rs.next()) {
                set.add(rs.getString("TABLE_NAME"));
            }
        }
        return set;
    }

    /**
     * 获取建表语句
     * @param conn
     * @param tableName
     * @return
     * @throws SQLException
     */
    public static String getCreateTableSql(Connection conn, String tableName) throws SQLException {
        String createTableSql = "";
        try(Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("SHOW CREATE TABLE " + tableName)) {
            while (rs.next()) {
                createTableSql = rs.getString("Create Table");
            }
        }
        return createTableSql;
    }

    /**
     * 获取建视图语句
     * @param conn
     * @param viewName
     * @return
     * @throws SQLException
     */
    public static String getCreateViewSql(Connection conn, String viewName) throws SQLException {
        String createViewSql = "";
        try(Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("SHOW CREATE VIEW " + viewName)) {
            while (rs.next()) {
                createViewSql = rs.getString("Create View");
            }
        }
        return createViewSql;
    }

    /**
     * 获取表字段
     * @param conn
     * @param tableName
     * @return
     * @throws SQLException
     */
    public static List<ColumnMeta> getTableColumns(Connection conn, String tableName) throws SQLException {
        List<ColumnMeta> list = new ArrayList<>();
        try(Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '" + tableName + "'")) {
            while (rs.next()) {
                ColumnMeta columnMeta = new ColumnMeta();
                columnMeta.setColumnName(rs.getString("COLUMN_NAME"));
                columnMeta.setColumnType(rs.getString("COLUMN_TYPE"));
                columnMeta.setIsNullable(rs.getString("IS_NULLABLE"));
                columnMeta.setColumnKey(rs.getString("COLUMN_KEY"));
                columnMeta.setColumnDefault(rs.getString("COLUMN_DEFAULT"));
                columnMeta.setColumnComment(rs.getString("COLUMN_COMMENT"));
                columnMeta.setCharacterSetName(rs.getString("CHARACTER_SET_NAME"));
                columnMeta.setCollationName(rs.getString("COLLATION_NAME"));
                list.add(columnMeta);
            }
        }
        return list;
    }

    /**
     * 获取表索引
     * @param conn
     * @param tableName
     * @return
     * @throws SQLException
     */
    public static List<IndexMeta> getTableIndexs(Connection conn, String tableName) throws SQLException {
        List<IndexMeta> list = new ArrayList<>();
        try(Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("SHOW INDEX FROM " + tableName)) {
            while (rs.next()) {
                IndexMeta indexMeta = new IndexMeta();
                indexMeta.setKeyName(rs.getString("Key_name"));
                indexMeta.setColumnName(rs.getString("Column_name"));
                indexMeta.setIndexType(rs.getString("Index_type"));
                indexMeta.setNonUnique(rs.getInt("Non_unique"));
                indexMeta.setSeqInIndex(rs.getInt("Seq_in_index"));
                list.add(indexMeta);
            }
        }
        return list;
    }


    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/cipos-work?generateSimpleParameterMetadata=true&serverTimezone=Asia/Shanghai&useSSL=false";
        String username = "root";
        String password = "root";
        try(Connection conn = DriverManager.getConnection(url, username, password)) {
            // Set<String> tables = getTables(conn);
            // tables.forEach(System.out::println);

            // String createTableSql = getCreateTableSql(conn, "t_enterprise");
            // FileUtil.createSqlScript("user_table_create", Arrays.asList(createTableSql));

            List<ColumnMeta> columnMetaList = getTableColumns(conn, "T_USER");
            columnMetaList.forEach(System.out::println);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
